﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace DAL
{
    public class ThongKeQuery : DataProvide
    {
        public static List<ThongKeDAL> LayDsThongKe()
        {
            SqlConnection chuoiKetNoi = null;
            List<ThongKeDAL> dsThongKe = new List<ThongKeDAL>();
            try
            {
                chuoiKetNoi = KetNoi();
                string sqlString = String.Format(@"Select * From ThongKeBaoCao");

                SqlCommand lenh = new SqlCommand(sqlString, chuoiKetNoi);

                SqlDataReader dr = lenh.ExecuteReader();
                while (dr.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = dr.GetString(0);
                    if (!dr.IsDBNull(1))
                        tk.NgayThongKe = dr.GetDateTime(1);
                    dsThongKe.Add(tk);
                }

            }
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsThongKe;
        }

        public static List<ThongKeDAL> LayDsChitietThongKe()
        {
            SqlConnection chuoiKetNoi = null;
            List<ThongKeDAL> dsChitietThongKe = new List<ThongKeDAL>();
            try
            {
                chuoiKetNoi = KetNoi();
                string sqlString = String.Format(@"Select CTThongKeBaoCao.MaTKBC, CTThongKeBaoCao.MaDonDatHang, CTThongKeBaoCao.TongThu, CTThongKeBaoCao.TongChi, CTThongKeBaoCao.LoiNhuan, ThongKeBaoCao.NgayTKBC From CTThongKeBaoCao, ThongKeBaoCao where CTThongKeBaoCao.MaTKBC = ThongKeBaoCao.MaTKBC");

                SqlCommand lenh = new SqlCommand(sqlString, chuoiKetNoi);

                SqlDataReader dr = lenh.ExecuteReader();

                while (dr.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = dr.GetString(0);
                    if (!dr.IsDBNull(1))
                        tk.MaDonDatHang = dr.GetInt32(1);
                    if (!dr.IsDBNull(2))
                        tk.TongThu = float.Parse(dr.GetDouble(2).ToString());
                    if (!dr.IsDBNull(3))
                        tk.TongChi = float.Parse(dr.GetDouble(3).ToString());
                    if (!dr.IsDBNull(4))
                        tk.LoiNhuan = float.Parse(dr.GetDouble(4).ToString());
                    if (!dr.IsDBNull(5))
                        tk.NgayThongKe = dr.GetDateTime(5);
                    dsChitietThongKe.Add(tk);
                }
            }
            catch
            {
                dsChitietThongKe = new List<ThongKeDAL>();
            }
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsChitietThongKe;
        }

        public static List<ThongKeDAL> ThongKeTheoDK(string thongke, string dondathang, string loinhuan)
        {
            SqlConnection ketNoi = null;
            List<ThongKeDAL> dsThongKe = new List<ThongKeDAL>();
            try
            {
                ketNoi = KetNoi();

                string insertCommand = "Select CTThongKeBaoCao.MaTKBC, CTThongKeBaoCao.MaDonDatHang, CTThongKeBaoCao.TongThu, CTThongKeBaoCao.TongChi, CTThongKeBaoCao.LoiNhuan, ThongKeBaoCao.NgayTKBC from CTThongKeBaoCao, ThongKeBaoCao where ThongKeBaoCao.MaTKBC = CTThongKeBaoCao.MaTKBC and CTThongKeBaoCao.MaTKBC like '%" + thongke + "%' and CTThongKeBaoCao.MaDonDatHang like '%" + dondathang + "%'";
                if (loinhuan == "<10 000")
                {
                    insertCommand = insertCommand + " and CTThongKeBaoCao.LoiNhuan < 10000";
                }
                if (loinhuan == "10 000 - 50 000")
                {
                    insertCommand = insertCommand + " and CTThongKeBaoCao.LoiNhuan between 10000 and 50000";
                }
                if (loinhuan == ">50 000")
                {
                    insertCommand = insertCommand + " and CTThongKeBaoCao.LoiNhuan > 50000";
                }

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                SqlDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        tk.MaDonDatHang = boDoc.GetInt32(1);
                    if (!boDoc.IsDBNull(2))
                        tk.TongThu = float.Parse(boDoc.GetDouble(2).ToString());
                    if (!boDoc.IsDBNull(3))
                        tk.TongChi = float.Parse(boDoc.GetDouble(3).ToString());
                    if (!boDoc.IsDBNull(4))
                        tk.LoiNhuan = float.Parse(boDoc.GetDouble(4).ToString());
                    if (!boDoc.IsDBNull(5))
                        tk.NgayThongKe = boDoc.GetDateTime(5);
                    dsThongKe.Add(tk);
                }
            }
            catch (Exception)
            {
                dsThongKe = new List<ThongKeDAL>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsThongKe;
        }

        public static List<ThongKeDAL> tim_maTK(string maTK)
        {
            SqlConnection ketNoi = null;
            List<ThongKeDAL> dsThongKe = new List<ThongKeDAL>();
            try
            {
                ketNoi = KetNoi();

                string insertCommand = "Select CTThongKeBaoCao.MaTKBC, CTThongKeBaoCao.MaDonDatHang, CTThongKeBaoCao.TongThu, CTThongKeBaoCao.TongChi, CTThongKeBaoCao.LoiNhuan, ThongKeBaoCao.NgayTKBC from CTThongKeBaoCao, ThongKeBaoCao where ThongKeBaoCao.MaTKBC = CTThongKeBaoCao.MaTKBC and CTThongKeBaoCao.MaTKBC like '%" + maTK + "%'";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                SqlDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        tk.MaDonDatHang = boDoc.GetInt32(1);
                    if (!boDoc.IsDBNull(2))
                        tk.TongThu = float.Parse(boDoc.GetDouble(2).ToString());
                    if (!boDoc.IsDBNull(3))
                        tk.TongChi = float.Parse(boDoc.GetDouble(3).ToString());
                    if (!boDoc.IsDBNull(4))
                        tk.LoiNhuan = float.Parse(boDoc.GetDouble(4).ToString());
                    if (!boDoc.IsDBNull(5))
                        tk.NgayThongKe = boDoc.GetDateTime(5);
                    dsThongKe.Add(tk);
                }
            }
            catch (Exception)
            {
                dsThongKe = new List<ThongKeDAL>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsThongKe;
        }
        public static List<ThongKeDAL> tim_NgayThangNam(string ngay, string thang, string nam, string matk)
        {
            SqlConnection ketNoi = null;
            List<ThongKeDAL> dsThongKe = new List<ThongKeDAL>();
            try
            {
                ketNoi = KetNoi();

                string insertCommand = "Select * from ThongKeBaoCao where MaTKBC like '%"+ matk +"%' ";
                if (ngay != "")
                {
                    insertCommand = insertCommand + "and Day(ThongKeBaoCao.NgayTKBC) = '" + ngay + "'";
                }
                if(thang != "")
                {
                    insertCommand = insertCommand + "and Month(ThongKeBaoCao.NgayTKBC) = '" + thang + "'";
                }
                if (nam != "")
                {
                    insertCommand = insertCommand + "and Year(ThongKeBaoCao.NgayTKBC) = '" + nam + "'";
                }
                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                SqlDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        tk.NgayThongKe = boDoc.GetDateTime(1);
                    dsThongKe.Add(tk);
                }
            }
            catch (Exception)
            {
                dsThongKe = new List<ThongKeDAL>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsThongKe;
        }
    }
}
